/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.AccountDTO;
import east2west.interfaces.AccountInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author thanhluan
 */
public class AccountDAO implements AccountInterface {

    Connection con = null;
    AccountDTO acc = null;
    CallableStatement call = null;

    public ArrayList<AccountDTO> readByAll() {
        ArrayList<AccountDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Account");
            ResultSet rs = call.executeQuery();
            list = new ArrayList<AccountDTO>();
            while (rs.next()) {
                acc = new AccountDTO();
                acc.setUserName(rs.getString(1));
                acc.setPassWord(rs.getString(2));
                int roleId = (rs.getInt(3));
                RoleDAO roleDAO = new RoleDAO();
                acc.setRoleDTO(roleDAO.findById(roleId));
                acc.setFullName(rs.getString(4));
                acc.setEmail(rs.getString(5));
                acc.setPhoneNumber(rs.getString(6));
                acc.setAddress(rs.getString(7));
                list.add(acc);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            if (con != null) {
                DatabaseUtil.closeConnection(con);
            }
            return null;
        }


    }

    public String readPassWord(String userName) {
        try {
            String pass = "";
            con = DatabaseUtil.getConnection();
            PreparedStatement pst = con.prepareStatement("select PassWord from V_Account where UserName = ? ");
            pst.setString(1, userName);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                pass = rs.getString(1);
            }
            pst.close();
            pst.close();
            return pass;
        } catch (SQLException ex) {
            return null;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public AccountDTO findByUserName(String userName) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Account where UserName = ?");
            call.setString(1, userName);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                acc = new AccountDTO();
                acc.setUserName(rs.getString(1));
                acc.setPassWord(rs.getString(2));
                int roleId = (rs.getInt(3));
                RoleDAO roleDAO = new RoleDAO();
                acc.setRoleDTO(roleDAO.findById(roleId));
                acc.setFullName(rs.getString(4));
                acc.setEmail(rs.getString(5));
                acc.setPhoneNumber(rs.getString(6));
                acc.setAddress(rs.getString(7));
            }
            rs.close();
            call.close();
            return acc;
        } catch (SQLException ex) {
            return null;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<AccountDTO> findByRole(int roleId) {
        ArrayList<AccountDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Account_FindRoId(?)}");
            call.setInt(3, roleId);
            ResultSet rs = call.executeQuery();
            list = new ArrayList<AccountDTO>();
            while (rs.next()) {
                acc = new AccountDTO();
                acc.setUserName(rs.getString(1));
                acc.setPassWord(rs.getString(2));
                acc.setRoleId(rs.getInt(3));
                acc.setFullName(rs.getString(4));
                acc.setEmail(rs.getString(5));
                acc.setPhoneNumber(rs.getString(6));
                acc.setAddress(rs.getString(7));

                list.add(acc);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<AccountDTO> findByFullName(String fullName) {
        ArrayList<AccountDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Account_FindFullName(?)}");
            call.setString(4, fullName);
            ResultSet rs = call.executeQuery();
            list = new ArrayList<AccountDTO>();
            while (rs.next()) {
                acc = new AccountDTO();
                acc.setUserName(rs.getString(1));
                acc.setPassWord(rs.getString(2));
                acc.setRoleId(rs.getInt(3));
                acc.setFullName(rs.getString(4));
                acc.setEmail(rs.getString(5));
                acc.setPhoneNumber(rs.getString(6));
                acc.setAddress(rs.getString(7));

                list.add(acc);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean create(AccountDTO acc) {
        try {

            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_Account(?,?,?,?,?,?,?)}");
            call.setString(1, acc.getUserName());
            call.setString(2, acc.getPassWord());
            call.setInt(3, acc.getRoleId());
            call.setString(4, acc.getFullName());
            call.setString(5, acc.getEmail());
            call.setString(6, acc.getPhoneNumber());
            call.setString(7, acc.getAddress());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(AccountDTO acc) {
        try {

            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_Account(?,?,?,?,?,?,?)}");
            call.setString(1, acc.getUserName());
            call.setString(2, acc.getPassWord());
            call.setInt(3, acc.getRoleId());
            call.setString(4, acc.getFullName());
            call.setString(5, acc.getEmail());
            call.setString(6, acc.getPhoneNumber());
            call.setString(7, acc.getAddress());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(AccountDTO acc) {
        try {
            acc = new AccountDTO();
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Account(?)}");
            call.setString(1, acc.getUserName());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(String userName) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Account(?)}");
            call.setString(1, userName);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }
}
